﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

//------------------------------------------------------
//クラス：H3A1 07
//名　前：仮屋　智央
//内　容：遅刻を記録する画面
//　　　：
//------------------------------------------------------

namespace h3a1_felica2
{
    public partial class Form5 : Form
    {
        string uri = System.Configuration.ConfigurationSettings.AppSettings["uri"];
        string database = System.Configuration.ConfigurationSettings.AppSettings["database"];
        string sql_text;
        MySqlConnection con;
        MySqlDataAdapter dAdpt;
        DataSet dSet;
        string Conditions2 = "";
        int[] class_id = new int[Parameter.selected_class.Count];

        public Form5(Form4 form4)
        {
            InitializeComponent();
            this.label3.Text = "科目";
            this.label4.Text = "クラス";
            this.label5.Text = Parameter.class_subject_name;
            this.label6.Text = "";

            for (int i = 0; i < Parameter.selected_class.Count; i++)
            {
                this.label6.Text += Parameter.selected_class[i] + " ";
                try
                {
                    MySqlConnection cn;
                    MySqlCommand cmd;
                    MySqlDataReader reader;
                    cn = new MySqlConnection();
                    cn.ConnectionString = "Server="+uri+";Uid=root;Pwd=mysql;database=" + database + ";";
                    cmd = new MySqlCommand();
                    cmd.Connection = cn;
                    cn.Open();

                    //選択したクラス名からクラスIDを取得
                    cmd.CommandText = "select class_id from class where class_name = '" + Parameter.selected_class[i] + "';";
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        class_id[i] = int.Parse(reader[0].ToString());
                    }
                    reader.Close();
                    cn.Close();
                }
                catch (MySqlException e1)
                {
                    MessageBox.Show(e1.Message);
                }
            }
            this.FormBorderStyle = FormBorderStyle.FixedSingle;
            this.Text = "遅刻";
            label1.Text = "カードをかざしてください";
            this.label2.Text = Parameter.lecturer_name;
            button1.Text = "終了";
            //今日の日付を取得
            string today = System.DateTime.Now.ToString("yyyy/MM/dd");
            today = today.Replace("/", "-");

            for (int i = 0; i < class_id.Length; i++)
            {
                if (i == 0)
                {
                    if (class_id.Length > 0)
                    {
                        Conditions2 += "(";
                    }
                    Conditions2 += " student.class_id = " + class_id[i];
                }
                else
                {
                    Conditions2 += " or student.class_id = " + class_id[i];
                }
            }
            if (class_id.Length > 0)
            {
                Conditions2 += " )";
            }

            //時限の条件
            sql_text = "select student.student_id,class.class_name,student.attendance_number,student.student_name,attendance_records.date,attendance_status.status_name,student.felica_id,attendance_records.status_id,class.class_id from student" +
                " left join class on student.class_id = class.class_id" +
                " left join attendance_records on student.student_id = attendance_records.student_id" +
                " and attendance_records.date = '" + today + "'" +
                " and attendance_records.hour_id = " + Parameter.hour_id +
                " and attendance_records.class_subject_id = " + Parameter.class_sublect_id +
                " left join attendance_status on attendance_records.status_id = attendance_status.status_id" +
                " where " + Conditions2 + " and delete_flag = 0 order by student.class_id";

            try
            {
                con = new MySqlConnection();
                con.ConnectionString = "Server="+uri+";Uid=root;Pwd=mysql;database=" + database + ";";
                dSet = new DataSet(database);
                dAdpt = new MySqlDataAdapter(sql_text, con);
                dAdpt.Fill(dSet, "student");

                //データグリッドビューに表示
                dataGridView1.DataSource = dSet.Tables[0];
                //データグリッドビューの行のヘッダを非表示
                dataGridView1.RowHeadersVisible = false;

                //DataGridView1のはじめの列のテキストを変更する
                dataGridView1.Columns[0].HeaderText = "学籍番号";
                dataGridView1.Columns[1].HeaderText = "クラス";
                dataGridView1.Columns[2].HeaderText = "番号";
                dataGridView1.Columns[3].HeaderText = "名前";
                dataGridView1.Columns[4].HeaderText = "日付";
                dataGridView1.Columns[5].HeaderText = "状況";

                dataGridView1.Columns["felica_id"].Visible = false;
                dataGridView1.Columns["status_id"].Visible = false;
                dataGridView1.Columns["class_id"].Visible = false;

                dataGridView1.Font = new Font(dataGridView1.Font.Name, 12);
                dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            }
            catch (MySqlException e1)
            {
                MessageBox.Show(e1.Message);
            }
            finally
            {
                con.Close();
            }
            this.FormClosing += new FormClosingEventHandler(Form5_FormClosing);

        }

        private void Form5_FormClosing(object sender, FormClosingEventArgs e)
        {
            Application.Exit();
        }

        private void Form5_Load(object sender, EventArgs e)
        {
            this.timer1.Interval = 500;
            this.timer1.Enabled = true;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            try
            {
                using (FelicaLib.Felica f = new FelicaLib.Felica())
                {
                    //ポーリング
                    f.Polling(0xFFFF);
                    byte[] data = f.IDm();

                    string dataStr = "";
                    for (int i = 0; i < data.Length; i++)
                    {
                        dataStr += data[i].ToString("X2");
                    }

                    if (dataStr != null)
                    {
                        this.timer1.Enabled = false; //タイマー機能停止
                        //FeliCaIDの検索
                        try
                        {
                            DataRow[] dataRows = dSet.Tables["student"].Select("felica_id = '" + dataStr.ToString() + "'");

                            if (dataRows.Length > 0)
                            {
                                //すでに出席処理済のレコードは処理しない
                                if (dataRows[0]["status_id"].ToString() != "1")
                                {
                                    //今日の日付を取得
                                    string today = System.DateTime.Now.ToString("yyyy/MM/dd");
                                    today = today.Replace("/", "-");
                                    try
                                    {
                                        con.ConnectionString = "Server="+uri+";Uid=root;Pwd=mysql;database=" + database + ";";
                                        con.Open();
                                        string insert_str = "insert into attendance_records(" +
                                        "class_id,student_id,date,hour_id,class_subject_id,status_id,memo,lecturer_id)" +
                                        "values(" + dataRows[0]["class_id"] + "," + dataRows[0]["student_id"] + ",'" + today + "'," + Parameter.hour_id + "," + Parameter.class_sublect_id + "," + 2 + ",''," + Parameter.lecturer_id + ")";
                                        MySqlCommand command = new MySqlCommand(insert_str, con);
                                        Int32 ret = command.ExecuteNonQuery();
                                        con.Close();
                                        if (ret > 0)
                                        {
                                            //認証成功の音を出力
                                            SoundUtils su = new SoundUtils();
                                            su.PlaySound("ok");

                                            con = new MySqlConnection();
                                            con.ConnectionString = "Server="+uri+";Uid=root;Pwd=mysql;database=" + database + ";";
                                            dSet = new DataSet(database);
                                            dAdpt = new MySqlDataAdapter(sql_text, con);
                                            dAdpt.Fill(dSet, "student");

                                            //データグリッドビューの色変更
                                            dataGridView1.DefaultCellStyle.BackColor = Color.Aqua;
                                            //データグリッドビューに表示
                                            dataGridView1.DataSource = dSet.Tables[0];
                                            //データグリッドビューの行のヘッダを非表示
                                            dataGridView1.RowHeadersVisible = false;

                                            dataGridView1.Columns["felica_id"].Visible = false;
                                            dataGridView1.Columns["status_id"].Visible = false;
                                            dataGridView1.Columns["class_id"].Visible = false;
                                            con.Close();

                                        }
                                        else
                                        {
                                            //認証失敗の音を出力
                                            SoundUtils su = new SoundUtils();
                                            su.PlaySound("ng");

                                            con.Close();
                                        }


                                    }
                                    catch (MySqlException e1)
                                    {
                                        MessageBox.Show(e1.Message);
                                        con.Close();
                                    }
                                }
                                else
                                {
                                    //認証失敗の音を出力
                                    SoundUtils su = new SoundUtils();
                                    su.PlaySound("ng");
                                }
                            }
                            else
                            {
                                //認証失敗の音を出力
                                SoundUtils su = new SoundUtils();
                                su.PlaySound("ng");
                            }

                        }
                        catch (NullReferenceException e1)
                        {
                            MessageBox.Show(e1.Message);
                        }
                        catch (Exception e1)
                        {
                            MessageBox.Show(e1.Message);
                        }
                        finally
                        {
                            this.timer1.Enabled = true; //タイマー機能再開
                        }
                    }


                }
            }
            catch (Exception e1)
            {
                
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("よろしいですか？", "終了確認", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.Yes)
            {

                string today = System.DateTime.Now.ToString("yyyy/MM/dd");
                today = today.Replace("/", "-");

                try
                {
                    //終了処理としてまだ出席になってない生徒を一括でＤＢに欠課で登録する
                    con = new MySqlConnection();
                    con.ConnectionString = "Server="+uri+";Uid=root;Pwd=mysql;database=" + database + ";";
                    con.Open();

                    //出席処理をしていない生徒の一覧を取得する
                    DataRow[] dataRows = dSet.Tables["student"].Select("status_id is null");
                    
                    string[] insert_sql = new string[dataRows.Length]; //人数分の配列生成　配列にする必要なかったかも・・・

                    for (int i = 0; i < dataRows.Length; i++)
                    {
                        //レコード追加用のSQL文作成
                        insert_sql[i] += "insert into attendance_records(class_id,student_id,date,hour_id,class_subject_id,status_id,memo,lecturer_id)" +
                            " values (" + dataRows[i]["class_id"] + "," + dataRows[i]["student_id"] + ",'" + today + "'," + Parameter.hour_id + "," + Parameter.class_sublect_id + ",3,''," + Parameter.lecturer_id + ")";

                        //SQL文と接続情報を指定し、MySQLコマンドを作成
                        MySqlCommand cmd = new MySqlCommand(insert_sql[i], con);
                        //戻り値なしでコマンドを実行
                        cmd.ExecuteNonQuery();

                    }
                }
                catch (MySqlException e1)
                {
                    MessageBox.Show("登録に失敗しました");
                }
                finally
                {
                    con.Close();
                    this.Hide();
                    Application.Exit();
                }
            }

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }
    }
}
